﻿using System;
using System.Linq;
using System.Collections.Generic;
using System.Data.SqlClient;
using DM4;
using sscore;
using sscore.Utils;
using System.Data;

namespace DM4
{
    public partial class DM4
    {
        #region MailSenders

        public MailSender GetMailSender(string name)
        {
            return GetMailSender(DM4Consts.DefaultNULLId, name);
        }

        public MailSender GetMailSender(int id, string name = "")
        {
            const string funcName = "GetMailSender";
            BeginFunction(funcName);
            MailSender si = new MailSender();

            string sql = name == ""
                ? String.Format("select * from {0} where {1} = {2}", Tn.MailSenders, CnMailSenders.Id, id)
                : String.Format("select * from {0} where LTRIM(RTRIM(UPPER({1}))) = '{2}'", Tn.MailSenders, CnMailSenders.Mail, name.Trim().ToUpper());

            WriteDebugLog(sql);
            using (SqlCommand sel = new SqlCommand(sql, _conn))
            {
                using (SqlDataReader reader = sel.ExecuteReader())
                {
                    if (reader.HasRows)
                        try
                        {
                            reader.Read();
                            si.Id = SysUtils.ConvertFromDBVal<int>(reader[CnMailSenders.Id]);
                            si.Mail = SysUtils.ConvertFromDBVal<string>(reader[CnMailSenders.Mail]);
                        }
                        catch (Exception ex)
                        {
                            WriteLog(String.Format("Error occurred while {0}: Id [{1}], [{2}] {3}", funcName, id, name, ex.Message), MessageType.Error);
                        }
                }
            }
            EndFunction(funcName);
            return si;
        }

        public XMLInfo GetMailSendersList(string usrSessionId)
        {
            const string funcName = "GetMailSendersList";
            BeginFunction(funcName);

            OperationResult userIdOpResult = GetUserIdBySession(usrSessionId);
            XMLInfo xi = new XMLInfo(userIdOpResult);

            if (!userIdOpResult)
            {
                WriteLog(userIdOpResult.Message, MessageType.Error);
                return xi;
            }

            const string sql = "select * from " + Tn.MailSenders;
            WriteDebugLog(sql);

            try
            {
                using (SqlCommand sel = new SqlCommand(sql, _conn))
                {
                    using (SqlDataReader reader = sel.ExecuteReader())
                    {
                        try
                        {
                            WriteLog(funcName + " operation complete");
                            xi.Load(reader, Tn.MailSenders);
                        }
                        catch (Exception ex)
                        {
                            WriteLog(ex.Message, MessageType.Error);
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                WriteLog(ex.Message, MessageType.Error);
            }

            EndFunction(funcName);
            return xi;
        }

        public OperationResult AddMailSender(string usrSessionId, string mail)
        {
            const string entityName = "mail sender";
            const string funcName = "AddMailSender";

            BeginFunction(funcName);

            OperationResult userIdOpResult = GetUserIdBySession(usrSessionId);
            if (!userIdOpResult) return userIdOpResult;
            OperationResult result = new OperationResult(userIdOpResult, DM4Consts.ErrorUnknown);

            WriteLog(String.Format("Attempt to add {0}...", entityName));
            try
            {
                MailSender ms = GetMailSender(mail);
                if (String.IsNullOrEmpty(ms.Mail))
                {
                    string sql = String.Format("insert  into  {0} ([{1}]) values ('{2}')"
                        , Tn.MailSenders
                        , CnMailSenders.Mail, mail);

                    WriteDebugLog(sql);

                    using (SqlCommand sc = new SqlCommand(sql, _conn))
                    {
                        sc.ExecuteNonQuery();
                    }
                    ms = GetMailSender(mail);
                    result.Result = ms.Id;
                    result.Message = String.Format("{0} [{1}] added", entityName, mail);
                    WriteLog(result.Message);
                }
                else
                {
                    result.Result = DM4Consts.ErrorEntityWithSomeNameAlreadyExists;
                    result.Message = String.Format("{0} [{1}] already exists", entityName, mail);
                    WriteLog(result.Message, MessageType.Error);
                }
            }
            catch (Exception ex)
            {
                WriteLog(ex.Message, MessageType.Error);
            }

            EndFunction(funcName);
            return result;
        }

        public OperationResult EditMailSender(string usrSessionId, int id, string mail)
        {
            const string entityName = "mail sender";
            const string funcName = "DeleteMailSender";

            BeginFunction(funcName);
            OperationResult userIdOpResult = GetUserIdBySession(usrSessionId);
            if (!userIdOpResult) return userIdOpResult;
            OperationResult result = new OperationResult(userIdOpResult, DM4Consts.ErrorUnknown);

            WriteLog(String.Format("Attempt to edit {0} with id [{1}]...", entityName, id));
            try
            {
                MailSender check = GetMailSender(mail);
                if ((!String.IsNullOrEmpty(check.Mail)) && (check.Id != id))
                {
                    result.Result = DM4Consts.ErrorEntityWithSomeNameAlreadyExists;
                    result.Message = String.Format("{0} [{1}] already exists", entityName, mail);
                    WriteLog(result.Message, MessageType.Error);
                }
                else
                {
                    MailSender ms = GetMailSender(id);
                    if (!String.IsNullOrEmpty(ms.Mail))
                    {
                        string sql = String.Format("update [{0}] set [{1}]='{2}' where [{3}] = {4}",
                            Tn.MailSenders
                            , CnMailSenders.Mail, mail
                            , CnMailSenders.Id, id);

                        WriteDebugLog(sql);
                        using (SqlCommand sc = new SqlCommand(sql, _conn))
                        {
                            sc.ExecuteNonQuery();
                        }

                        ms = GetMailSender(id);
                        result.Result = ms.Id;
                        result.Message = String.Format("{0} with id [{1}] modified", entityName, id);
                        WriteLog(result.Message);

                    }
                    else
                    {
                        result.Result = DM4Consts.ErrorUnknownEntity;
                        result.Message = String.Format("Unknown {0} with id [{1}]", entityName, id);
                        WriteLog(result.Message, MessageType.Error);
                    }
                }
            }
            catch (Exception ex)
            {
                WriteLog(ex.Message, MessageType.Error);
            }

            EndFunction(funcName);
            return result;
        }

        public OperationResult DeleteMailSender(string usrSessionId, int id)
        {
            const string funcName = "DeleteMailSender";
            const string tn = Tn.MailSenders;
            const string idColumnName = CnMailSenders.Id;
            const string entityName = "mail sender";

            BeginFunction(funcName);
            OperationResult userIdOpResult = GetUserIdBySession(usrSessionId);
            if (!userIdOpResult) return userIdOpResult;
            OperationResult result = new OperationResult(userIdOpResult, DM4Consts.ErrorUnknown);

            WriteLog(String.Format("Attempt to delete {0} [{1}]...", entityName, id));

            try
            {
                MailSender m = GetMailSender(id);
                if (!String.IsNullOrEmpty(m.Mail))
                {
                    string sql = String.Format("delete from [{0}] where [{1}] = {2}", tn, idColumnName, id);

                    WriteDebugLog(sql);
                    using (SqlCommand sc = new SqlCommand(sql, _conn))
                    {
                        sc.ExecuteNonQuery();
                    }

                    result.Result = DM4Consts.Ok;
                    result.Message = String.Format("{0} with id [{1}] deleted", entityName, id);
                    WriteLog(result.Message);
                }
                else
                {
                    result.Result = DM4Consts.ErrorUnknownEntity;
                    result.Message = String.Format("Unknown {0} with id [{1}]", entityName, id);
                    WriteLog(result.Message, MessageType.Error);
                }

            }
            catch (Exception ex)
            {
                WriteLog(ex.Message, MessageType.Error);
            }

            EndFunction(funcName);
            return result;
        }

        #endregion

        #region Letters

        public Letter GetLetter(int senderId, string description)
        {
            return GetLetter(DM4Consts.DefaultNULLId, senderId, description);
        }

        public Letter GetLetter(int id, int senderId = DM4Consts.DefaultNULLId, string description = "")
        {
            const string funcName = "GetLetter";
            BeginFunction(funcName);
            Letter l = new Letter();

            string sql = id == DM4Consts.DefaultNULLId
                ? String.Format("select * from {0} where LTRIM(RTRIM(UPPER({1}))) = '{2}' and {3} = {4}", Tn.MaiLetters
                , CnMailLetters.Description, description.Trim().ToUpper()
                , CnMailLetters.SenderId, senderId)
                : String.Format("select * from {0} where {1} = {2}", Tn.MaiLetters, CnMailLetters.Id, id);

            WriteDebugLog(sql);
            using (SqlCommand sel = new SqlCommand(sql, _conn))
            {
                using (SqlDataReader reader = sel.ExecuteReader())
                {
                    if (reader.HasRows)
                        try
                        {
                            reader.Read();
                            l.Id = SysUtils.ConvertFromDBVal<int>(reader[CnMailLetters.Id]);
                            l.SenderId = SysUtils.ConvertFromDBVal<int>(reader[CnMailLetters.SenderId]);
                            l.Description = SysUtils.ConvertFromDBVal<string>(reader[CnMailLetters.Description]);
                        }
                        catch (Exception ex)
                        {
                            WriteLog(String.Format("Error occurred while {0}: Id [{1}], [{2}] {3}", funcName, id, description, ex.Message), MessageType.Error);
                        }
                }
            }
            EndFunction(funcName);
            return l;
        }

        public XMLInfo GetLettersList(string usrSessionId, string expression = "")
        {
            const string funcName = "GetLettersList";
            BeginFunction(funcName);

            OperationResult userIdOpResult = GetUserIdBySession(usrSessionId);
            XMLInfo xi = new XMLInfo(userIdOpResult);
            if (!userIdOpResult)
            {
                WriteLog(userIdOpResult.Message, MessageType.Error);
                return xi;
            }

            string sql = String.Format("select * from {0} {1}", Tn.MaiLetters, expression);
            WriteDebugLog(sql);

            try
            {
                using (SqlCommand sel = new SqlCommand(sql, _conn))
                {
                    using (SqlDataReader reader = sel.ExecuteReader())
                    {
                        try
                        {
                            WriteLog(funcName + " operation complete");
                            xi.Load(reader, Tn.MaiLetters);
                        }
                        catch (Exception ex)
                        {
                            WriteLog(ex.Message, MessageType.Error);
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                WriteLog(ex.Message, MessageType.Error);
            }

            EndFunction(funcName);
            return xi;
        }

        public OperationResult AddLetter(string usrSessionId, int senderId, string description)
        {
            const string entityName = "letter";
            const string funcName = "AddLetter";

            BeginFunction(funcName);
            OperationResult userIdOpResult = GetUserIdBySession(usrSessionId);
            if (!userIdOpResult) return userIdOpResult;
            OperationResult result = new OperationResult(userIdOpResult, DM4Consts.ErrorUnknown);

            WriteLog(String.Format("Attempt to add {0}...", entityName));
            try
            {
                Letter l = GetLetter(senderId, description);
                if (l.Id == DM4Consts.DefaultNULLId)
                {
                    string sql = String.Format("insert  into  {0} ([{1}], [{2}]) values ({3}, '{4}' )"
                        , Tn.MaiLetters
                        , CnMailLetters.SenderId, CnMailLetters.Description
                        , senderId, description.Replace("'", "''"));

                    WriteDebugLog(sql);

                    using (SqlCommand sc = new SqlCommand(sql, _conn))
                    {
                        sc.ExecuteNonQuery();
                    }
                    l = GetLetter(senderId, description);
                    result.Result = l.Id;
                    result.Message = String.Format("{0} [{1}] for sender [{2}] added", entityName, description, senderId);
                    WriteLog(result.Message);
                }
                else
                {
                    result.Result = DM4Consts.ErrorEntityWithSomeNameAlreadyExists;
                    result.Message = String.Format("{0} [{1}] already exists", entityName, description);
                    WriteLog(result.Message, MessageType.Error);
                }
            }
            catch (Exception ex)
            {
                WriteLog(ex.Message, MessageType.Error);
            }

            EndFunction(funcName);
            return result;
        }

        public OperationResult EditLetter(string usrSessionId, int id, int senderId, string description)
        {
            const string entityName = "letter";
            const string funcName = "EditLetter";

            BeginFunction(funcName);
            OperationResult userIdOpResult = GetUserIdBySession(usrSessionId);
            if (!userIdOpResult) return userIdOpResult;
            OperationResult result = new OperationResult(userIdOpResult, DM4Consts.ErrorUnknown);

            WriteLog(String.Format("Attempt to edit {0} with id [{1}]...", entityName, id));
            try
            {
                Letter check = GetLetter(senderId, description);
                if ((!String.IsNullOrEmpty(check.Description)) && (check.Id != id))
                {
                    result.Result = DM4Consts.ErrorEntityWithSomeNameAlreadyExists;
                    result.Message = String.Format("{0} [{1}] already exists for sender [{2}]", entityName, description, senderId);
                    WriteLog(result.Message, MessageType.Error);
                }
                else
                {
                    Letter l = GetLetter(id);
                    if (l.Id != DM4Consts.DefaultNULLId)
                    {
                        string sql = String.Format("update [{0}] set [{1}] = {2}, [{3}] = '{4}' where [{5}] = {6}",
                            Tn.MaiLetters
                            , CnMailLetters.SenderId, senderId
                            , CnMailLetters.Description, description.Replace("'", "''")
                            , CnMailLetters.Id, id);

                        WriteDebugLog(sql);
                        using (SqlCommand sc = new SqlCommand(sql, _conn))
                        {
                            sc.ExecuteNonQuery();
                        }

                        l = GetLetter(id);
                        result.Result = l.Id;
                        result.Message = String.Format("{0} with id [{1}] modified", entityName, id);
                        WriteLog(result.Message);

                    }
                    else
                    {
                        result.Result = DM4Consts.ErrorUnknownEntity;
                        result.Message = String.Format("Unknown {0} with id [{1}]", entityName, id);
                        WriteLog(result.Message, MessageType.Error);
                    }
                }
            }
            catch (Exception ex)
            {
                WriteLog(ex.Message, MessageType.Error);
            }

            EndFunction(funcName);
            return result;
        }

        public OperationResult DeleteLetter(string usrSessionId, int id)
        {
            const string funcName = "DeleteLetter";
            const string tn = Tn.MaiLetters;
            const string idColumnName = CnMailLetters.Id;
            const string entityName = "letter";

            BeginFunction(funcName);
            OperationResult userIdOpResult = GetUserIdBySession(usrSessionId);
            if (!userIdOpResult) return userIdOpResult;
            OperationResult result = new OperationResult(userIdOpResult, DM4Consts.ErrorUnknown);

            WriteLog(String.Format("Attempt to delete {0} [{1}]...", entityName, id));

            try
            {
                Letter l = GetLetter(id);
                if (l.Id != DM4Consts.DefaultNULLId)
                {
                    string sql = String.Format("delete from [{0}] where [{1}] = {2}", tn, idColumnName, id);

                    WriteDebugLog(sql);
                    using (SqlCommand sc = new SqlCommand(sql, _conn))
                    {
                        sc.ExecuteNonQuery();
                    }

                    result.Result = DM4Consts.Ok;
                    result.Message = String.Format("{0} with id [{1}] deleted", entityName, id);
                    WriteLog(result.Message);
                }
                else
                {
                    result.Result = DM4Consts.ErrorUnknownEntity;
                    result.Message = String.Format("Unknown {0} with id [{1}]", entityName, id);
                    WriteLog(result.Message, MessageType.Error);
                }

            }
            catch (Exception ex)
            {
                WriteLog(ex.Message, MessageType.Error);
            }

            EndFunction(funcName);
            return result;
        }
        #endregion

        #region Mail Rules

        public MailRule GetMailRule(string rule, MailRuleType mrt = MailRuleType.Empty)
        {
            return GetMailRule(DM4Consts.DefaultNULLId, rule, mrt);
        }

        public MailRule GetMailRule(int id, string rule = "", MailRuleType mrt = MailRuleType.Empty)
        {
            const string funcName = "GetMailRule";
            BeginFunction(funcName);
            MailRule mr = new MailRule();

            string sql = rule == ""
                ? String.Format("select * from {0} where {1} = {2}", Tn.MailRules, CnMailRules.Id, id)
                : String.Format("select * from {0} where {1} = '{2}' and {3} = '{4}'", Tn.MailRules
                , CnMailRules.Rule, rule
                , CnMailRules.RuleType, MailRuleValues.Dict[mrt]);

            WriteDebugLog(sql);
            using (SqlCommand sel = new SqlCommand(sql, _conn))
            {
                using (SqlDataReader reader = sel.ExecuteReader())
                {
                    if (reader.HasRows)
                        try
                        {
                            reader.Read();
                            mr.Id = SysUtils.ConvertFromDBVal<int>(reader[CnMailRules.Id]);
                            mr.RuleName = SysUtils.ConvertFromDBVal<string>(reader[CnMailRules.RuleName]);
                            mr.Rule = SysUtils.ConvertFromDBVal<string>(reader[CnMailRules.Rule]);
                            mr.RuleType = MailRuleValues.Dict.First(kvp => kvp.Value.Equals(
                                SysUtils.ConvertFromDBVal<string>(reader[CnMailRules.RuleType])
                                )).Key;
                        }
                        catch (Exception ex)
                        {
                            WriteLog(String.Format("Error occurred while {0}: Id [{1}], [{2}], [{3}] {4} {5}"
                                , funcName, id, rule, MailRuleValues.Dict[mrt]
                                , Environment.NewLine, ex.Message), MessageType.Error);
                        }
                }
            }
            EndFunction(funcName);
            return mr;
        }

        public XMLInfo GetMailRulesList(string usrSessionId, string expression = "")
        {
            const string funcName = "GetMailRulesList";
            BeginFunction(funcName);

            OperationResult userIdOpResult = GetUserIdBySession(usrSessionId);
            XMLInfo xi = new XMLInfo(userIdOpResult);

            if (!userIdOpResult)
            {
                WriteLog(userIdOpResult.Message, MessageType.Error);
                return xi;
            }

            string sql = String.Format("select * from {0} {1}", Tn.MailRules, expression);
            WriteDebugLog(sql);

            try
            {
                using (SqlCommand sel = new SqlCommand(sql, _conn))
                {
                    using (SqlDataReader reader = sel.ExecuteReader())
                    {
                        try
                        {
                            WriteLog(funcName + " operation complete");
                            xi.Load(reader, Tn.MailRules);
                        }
                        catch (Exception ex)
                        {
                            WriteLog(ex.Message, MessageType.Error);
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                WriteLog(ex.Message, MessageType.Error);
            }

            EndFunction(funcName);
            return xi;
        }

        public OperationResult AddMailRule(string usrSessionId, string ruleName, string rule, MailRuleType mrt)
        {
            const string entityName = "mail rule";
            const string funcName = "AddMailRule";

            BeginFunction(funcName);
            OperationResult userIdOpResult = GetUserIdBySession(usrSessionId);
            if (!userIdOpResult) return userIdOpResult;
            OperationResult result = new OperationResult(userIdOpResult, DM4Consts.ErrorUnknown);

            WriteLog(String.Format("Attempt to add {0}...", entityName));
            try
            {
                MailRule mr = GetMailRule(rule, mrt);
                if (mr.Id == DM4Consts.DefaultNULLId)
                {
                    string sql = String.Format("insert  into  {0} ([{1}], [{2}], [{3}]) values ('{4}', @{2}, '{5}' )"
                        , Tn.MailRules
                        , CnMailRules.RuleName, CnMailRules.Rule, CnMailRules.RuleType
                        , ruleName.Replace("'", "''"), MailRuleValues.Dict[mrt].Replace("'", "''"));

                    WriteDebugLog(sql);

                    using (SqlCommand sc = new SqlCommand(sql, _conn))
                    {

                        SqlParameter param = sc.Parameters.Add("@" + CnMailRules.Rule, SqlDbType.NVarChar);
                        param.Value = rule;
                        param.Size = -1;
                        sc.ExecuteNonQuery();
                    }
                    mr = GetMailRule(rule, mrt);
                    result.Result = mr.Id;
                    result.Message = String.Format("{0} [{1}] added", entityName, rule);
                    WriteLog(result.Message);
                }
                else
                {
                    result.Result = DM4Consts.ErrorEntityWithSomeNameAlreadyExists;
                    result.Message = String.Format("{0} [{1}][{2}] already exists", entityName, rule, mrt);
                    WriteLog(result.Message, MessageType.Error);
                }
            }
            catch (Exception ex)
            {
                WriteLog(ex.Message, MessageType.Error);
            }

            EndFunction(funcName);
            return result;
        }

        public OperationResult EditMailRule(string usrSessionId, int id, string ruleName, string rule, MailRuleType mrt)
        {
            const string entityName = "mail rule";
            const string funcName = "EditMailRule";

            BeginFunction(funcName);
            OperationResult userIdOpResult = GetUserIdBySession(usrSessionId);
            if (!userIdOpResult) return userIdOpResult;
            OperationResult result = new OperationResult(userIdOpResult, DM4Consts.ErrorUnknown);

            WriteLog(String.Format("Attempt to edit {0} with id [{1}]...", entityName, id));
            try
            {
                MailRule check = GetMailRule(rule, mrt);
                if ((!String.IsNullOrEmpty(check.Rule)) && (check.Id != id))
                {
                    result.Result = DM4Consts.ErrorEntityWithSomeNameAlreadyExists;
                    result.Message = String.Format("{0} [{1}][{2}] already exists", entityName, rule, mrt);
                    WriteLog(result.Message, MessageType.Error);
                }
                else
                {
                    MailRule mr = GetMailRule(id);
                    if (!String.IsNullOrEmpty(mr.Rule) && (mr.Id != DM4Consts.DefaultNULLId))
                    {
                        string sql = String.Format("update [{0}] set [{1}]='{2}', [{3}]=@{3},  [{4}]='{5}' where [{6}] = {7}",
                            Tn.MailRules
                            , CnMailRules.RuleName, ruleName.Replace("'", "''")
                            , CnMailRules.Rule
                            , CnMailRules.RuleType, MailRuleValues.Dict[mrt].Replace("'", "''")
                            , CnMailRules.Id, id);

                        WriteDebugLog(sql);
                        using (SqlCommand sc = new SqlCommand(sql, _conn))
                        {

                            SqlParameter param = sc.Parameters.Add("@" + CnMailRules.Rule, SqlDbType.NVarChar);
                            param.Value = rule;
                            param.Size = -1;

                            sc.ExecuteNonQuery();
                        }

                        mr = GetMailRule(id);
                        result.Result = mr.Id;
                        result.Message = String.Format("{0} with id [{1}] modified", entityName, id);
                        WriteLog(result.Message);

                    }
                    else
                    {
                        result.Result = DM4Consts.ErrorUnknownEntity;
                        result.Message = String.Format("Unknown {0} with id [{1}]", entityName, id);
                        WriteLog(result.Message, MessageType.Error);
                    }
                }
            }
            catch (Exception ex)
            {
                WriteLog(ex.Message, MessageType.Error);
            }

            EndFunction(funcName);
            return result;
        }

        public OperationResult DeleteMailRule(string usrSessionId, int id)
        {
            const string funcName = "DeleteMailRule";
            const string tn = Tn.MailRules;
            const string idColumnName = CnMailRules.Id;
            const string entityName = "mail rule";

            BeginFunction(funcName);
            OperationResult userIdOpResult = GetUserIdBySession(usrSessionId);
            if (!userIdOpResult) return userIdOpResult;
            OperationResult result = new OperationResult(userIdOpResult, DM4Consts.ErrorUnknown);
            WriteLog(String.Format("Attempt to delete {0} [{1}]...", entityName, id));

            try
            {
                MailRule m = GetMailRule(id);
                if (!String.IsNullOrEmpty(m.Rule))
                {
                    string sql = String.Format("delete from [{0}] where [{1}] = {2}", tn, idColumnName, id);

                    WriteDebugLog(sql);
                    using (SqlCommand sc = new SqlCommand(sql, _conn))
                    {
                        sc.ExecuteNonQuery();
                    }

                    result.Result = DM4Consts.Ok;
                    result.Message = String.Format("{0} with id [{1}] deleted", entityName, id);
                    WriteLog(result.Message);
                }
                else
                {
                    result.Result = DM4Consts.ErrorUnknownEntity;
                    result.Message = String.Format("Unknown {0} with id [{1}]", entityName, id);
                    WriteLog(result.Message, MessageType.Error);
                }

            }
            catch (Exception ex)
            {
                WriteLog(ex.Message, MessageType.Error);
            }

            EndFunction(funcName);
            return result;
        }
        #endregion

        #region mail_letters_rules

        public MailLettersRules GetMailLettersRule(int letterId, int ruleId)
        {
            return GetMailLettersRule(DM4Consts.DefaultNULLId, letterId, ruleId);
        }

        public MailLettersRules GetMailLettersRule(int id, int letterId = DM4Consts.DefaultNULLId, int ruleId = DM4Consts.DefaultNULLId)
        {
            const string funcName = "GetMailLettersRule";
            BeginFunction(funcName);
            MailLettersRules l = new MailLettersRules();

            string sql = id == DM4Consts.DefaultNULLId
                ? String.Format("select * from {0} where {1} = {2} and {3} = {4}"
                , Tn.MaiLettersRules
                , CnMailLettersRules.LetterId, letterId
                , CnMailLettersRules.RuleId, ruleId)
                : String.Format("select * from {0} where {1} = {2}", Tn.MaiLettersRules, CnMailLettersRules.Id, id);

            WriteDebugLog(sql);
            using (SqlCommand sel = new SqlCommand(sql, _conn))
            {
                using (SqlDataReader reader = sel.ExecuteReader())
                {
                    if (reader.HasRows)
                        try
                        {
                            reader.Read();
                            l.Id = SysUtils.ConvertFromDBVal<int>(reader[CnMailLettersRules.Id]);
                            l.LetterId = SysUtils.ConvertFromDBVal<int>(reader[CnMailLettersRules.LetterId]);
                            l.RuleId = SysUtils.ConvertFromDBVal<int>(reader[CnMailLettersRules.RuleId]);
                        }
                        catch (Exception ex)
                        {
                            WriteLog(String.Format("Error occurred while {0}: Id [{1}], [{2}], [{3}] {4}", funcName, id, letterId, ruleId, ex.Message), MessageType.Error);
                        }
                }
            }
            EndFunction(funcName);
            return l;
        }

        public XMLInfo GetMailLettersRulesList(string usrSessionId, int letterId)
        {
            const string funcName = "GetMailLettersRulesList";
            BeginFunction(funcName);

            OperationResult userIdOpResult = GetUserIdBySession(usrSessionId);
            XMLInfo xi = new XMLInfo(userIdOpResult);

            if (!userIdOpResult)
            {
                WriteLog(userIdOpResult.Message, MessageType.Error);
                return xi;
            }

            string sql = String.Format(" select {0}.*"
                  + " from {0}, {1}"
                  + " where {1}.{2} = {0}.{3}"
                  + " and {1}.{4} = {5}", Tn.MailRules, Tn.MaiLettersRules,
                  CnMailLettersRules.RuleId, CnMailRules.Id,
                  CnMailLettersRules.LetterId, letterId);

            WriteDebugLog(sql);

            try
            {
                using (SqlCommand sel = new SqlCommand(sql, _conn))
                {
                    using (SqlDataReader reader = sel.ExecuteReader())
                    {
                        try
                        {
                            WriteLog(funcName + " operation complete");
                            xi.Load(reader, Tn.MaiLettersRules);
                        }
                        catch (Exception ex)
                        {
                            WriteLog(ex.Message, MessageType.Error);
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                WriteLog(ex.Message, MessageType.Error);
            }

            EndFunction(funcName);
            return xi;
        }

        public OperationResult AddMailLettersRule(string usrSessionId, int letterId, int ruleId)
        {
            const string entityName = "MailLetters_Rule";
            const string funcName = "MailLettersRule";

            BeginFunction(funcName);
            OperationResult userIdOpResult = GetUserIdBySession(usrSessionId);
            if (!userIdOpResult) return userIdOpResult;
            OperationResult result = new OperationResult(userIdOpResult, DM4Consts.ErrorUnknown);

            WriteLog(String.Format("Attempt to add {0}...", entityName));
            try
            {
                MailLettersRules l = GetMailLettersRule(letterId, ruleId);
                if (l.Id == DM4Consts.DefaultNULLId)
                {
                    string sql = String.Format("insert  into {0} ([{1}], [{2}]) values ({3}, {4} )"
                        , Tn.MaiLettersRules
                        , CnMailLettersRules.LetterId, CnMailLettersRules.RuleId
                        , letterId, ruleId);

                    WriteDebugLog(sql);

                    using (SqlCommand sc = new SqlCommand(sql, _conn))
                    {
                        sc.ExecuteNonQuery();
                    }
                    l = GetMailLettersRule(letterId, ruleId);
                    result.Result = l.Id;
                    result.Message = String.Format("{0} for letter [{1}] and rule [{2}] added", entityName, letterId, ruleId);
                    WriteLog(result.Message);
                }
                else
                {
                    result.Result = DM4Consts.ErrorEntityWithSomeNameAlreadyExists;
                    result.Message = String.Format("{0} for letter [{1}] and rule [{2}] already exists", entityName, letterId, ruleId);
                    WriteLog(result.Message, MessageType.Error);
                }
            }
            catch (Exception ex)
            {
                WriteLog(ex.Message, MessageType.Error);
            }

            EndFunction(funcName);
            return result;
        }

        public OperationResult DeleteMailLettersRule(string usrSessionId, int letterId, int ruleId, int id = DM4Consts.DefaultNULLId)
        {
            const string funcName = "DeleteMailLettersRule";

            BeginFunction(funcName);
            OperationResult userIdOpResult = GetUserIdBySession(usrSessionId);
            if (!userIdOpResult) return userIdOpResult;
            OperationResult result = new OperationResult(userIdOpResult, DM4Consts.ErrorUnknown);
            WriteLog(String.Format("Attempt to delete rule {0} for letter {1}...", ruleId, letterId));

            try
            {
                MailLettersRules l = (id == DM4Consts.DefaultNULLId)
                    ? GetMailLettersRule(letterId, ruleId)
                    : GetMailLettersRule(id);

                if (l.Id != DM4Consts.DefaultNULLId)
                {
                    string sql = (id == DM4Consts.DefaultNULLId)
                     ? String.Format("delete from [{0}] where [{1}] = {2} and [{3}] = {4}", Tn.MaiLettersRules,
                        CnMailLettersRules.LetterId, letterId,
                        CnMailLettersRules.RuleId, ruleId)
                    : String.Format("delete from [{0}] where [{1}] = {2}", Tn.MaiLettersRules,
                        CnMailLettersRules.Id, id);

                    WriteDebugLog(sql);
                    using (SqlCommand sc = new SqlCommand(sql, _conn))
                    {
                        sc.ExecuteNonQuery();
                    }

                    result.Result = DM4Consts.Ok;
                    result.Message = String.Format("Rule {0} for letter {1} with id [{2}] deleted", ruleId, letterId, l.Id);
                    WriteLog(result.Message);
                }
                else
                {
                    result.Result = DM4Consts.ErrorUnknownEntity;
                    result.Message = String.Format("Unknown rule {0} for letter {1}", ruleId, letterId);
                    WriteLog(result.Message, MessageType.Error);
                }

            }
            catch (Exception ex)
            {
                WriteLog(ex.Message, MessageType.Error);
            }

            EndFunction(funcName);
            return result;
        }


        #endregion
    }
}
